package com.xuxueli.commontdemo.util;

import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 测试解析sql
 */
@Component
public class SQLParserUtils {

    public String dealSql(String sql) {
        List list = new ArrayList();
        sql = caseSql(sql);
        if (sql.startsWith("WITH")) {
            String[] strings = StringUtils.substringsBetween(sql, "(", ")");
            String s = StringUtils.substringBetween(sql, "WITH", "AS");
            int length = sql.split("SELECT").length;
            String s1 = sql.split("SELECT")[length - 1];
            list.add(s.trim());

            if (StringUtils.substringsBetween(sql, "),", "AS") != null) {
                Arrays.stream(StringUtils.substringsBetween(sql, "),", "AS")).collect(Collectors.toList()).forEach(
                        i -> {
                            list.add(i.trim());
                        }
                );
            }
            if (list.size() > 1) {
                for (int i = 0; i < list.size(); i++) {
                    String mappingTableName = s1.substring(s1.indexOf(String.valueOf(list.get(i))) + String.valueOf(list.get(i)).length() + 1, s1.indexOf(String.valueOf(list.get(i))) + String.valueOf(list.get(i)).length() + 2);
                    String tableName = strings[i].split("FROM")[1].trim().split(" ")[0];
                    s1 = s1.replace(mappingTableName.trim() + ".", tableName.trim() + ".");
                }
            }
            for (int i = 0; i < list.size(); i++) {
                if (!s1.endsWith(")")) {
                    s1 = StringUtils.replaceOnce(s1, String.valueOf(list.get(i)), "(" + strings[i] + ")");
                } else {
                    s1 = StringUtils.replaceOnce(s1, String.valueOf(list.get(i)), "(" + strings[i]);
                }

            }
            sql = "SELECT " + s1;
        }

        return sql;
    }

    public String caseSql(String sql) {
        if (sql.startsWith("with ")) {
            sql = sql.replace("with ", "WITH ");
        }
        if (sql.contains("select ")) {
            sql = sql.replace("select ", "SELECT ");
        }
        if (sql.contains("where ")) {
            sql = sql.replace("where ", "WHERE ");
        }
        if (sql.contains("from ")) {
            sql = sql.replace("from ", "FROM ");
        }
        if (sql.contains("as ")) {
            sql = sql.replace("as ", "AS ");
        }

        return sql;
    }

    /**
     * 组装请求参数
     */
    public List<Map<String, Object>> assembleRequestParam(List<String> requestParamList, List<String> maybeEmptyParamList) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        requestParamList.forEach(requestParam -> {
            Map<String, Object> requestParamMap = new HashMap<>();
            requestParamMap.put("paramName", requestParam);
            requestParamMap.put("columnName", requestParam);
            requestParamMap.put("columnType", null);
            requestParamMap.put("columnComment", null);
            requestParamMap.put("paramType", "String");
            requestParamMap.put("dataType", null);
            requestParamMap.put("expression", null);
            requestParamMap.put("paramRequired", true);
            for (String s : maybeEmptyParamList) {
                if (s.contains(requestParam)) {
                    requestParamMap.put("paramRequired", false);
                }
            }
            dataList.add(requestParamMap);
        });
        return dataList;
    }

    /**
     * 组装返回参数
     */
    public List<Object> assembleResponseParam(List<String> responseParamList) {
        List<Object> dataList = new ArrayList<>();
        if (responseParamList.size() > 0) {
            responseParamList.forEach(responseParam -> {
                Map<String, Object> responseParamMap = new HashMap<>();
                responseParamMap.put("paramName", responseParam);
                responseParamMap.put("columnName", responseParam);
                dataList.add(responseParamMap);
            });
        }
        return dataList;
    }

    /**
     * 组装请求参数
     */
    public List<Map<String, Object>> assembleRequestParams(List<String> requestParamList, List<String> maybeEmptyParamList,Map<String,String> aliasMap) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        requestParamList.forEach(requestParam -> {
            Map<String, Object> requestParamMap = new HashMap<>();
            requestParamMap.put("paramName", requestParam);
            requestParamMap.put("columnName",  StringUtils.isNotBlank(aliasMap.get(requestParam)) ? aliasMap.get(requestParam) : requestParam);
            requestParamMap.put("columnType", null);
            requestParamMap.put("columnComment", null);
            requestParamMap.put("paramType", "String");
            requestParamMap.put("dataType", null);
            requestParamMap.put("expression", null);
            requestParamMap.put("paramRequired", true);
            for (String s : maybeEmptyParamList) {
                if (s.equals(requestParam)) {
                    requestParamMap.put("paramRequired", false);
                }
            }
            dataList.add(requestParamMap);
        });
        return dataList;
    }

    /**
     * 组装返回参数
     */
    public List<Object> assembleResponseParams(List<String> responseParamList,Map<String,String> aliasMap) {
        List<Object> dataList = new ArrayList<>();
        if (responseParamList.size() > 0) {
            responseParamList.forEach(responseParam -> {
                Map<String, Object> responseParamMap = new HashMap<>();
                responseParamMap.put("paramName", responseParam);
                responseParamMap.put("columnName", responseParam);
                dataList.add(responseParamMap);
            });
        }
        return dataList;
    }

    @Data
    public static class RequestParam {
        private String tableName; // 对应的表名
        private String columnName; // 字段名
        private String operator; // 操作符

        public RequestParam(String columnName) {
            this.columnName = columnName;
        }

        public RequestParam(String tableName, String columnName) {
            this.tableName = tableName;
            this.columnName = columnName;
        }
    }

    @Data
    public static class ResponseParam {
        private String tableName; // 对应的表名
        private String columnName; // 字段名
        private String function;  // 函数
        private String alias;  // 别名

        public ResponseParam(String columnName) {
            this(null, columnName);
        }

        public ResponseParam(String tableName, String columnName) {
            this.tableName = tableName;
            this.columnName = columnName;
        }
    }

    /**
     * columnType 映射出 paramType
     */
    public static String paramTypeMapping(String columnType) {
        if (null == columnType) {
            return "String";
        }
        if (columnType.equalsIgnoreCase("CHAR") || columnType.equalsIgnoreCase("VARCHAR") || columnType.equalsIgnoreCase("LONGVARCHAR")
                || columnType.toUpperCase().contains("CHAR")) {
            return "String";
        } else if (columnType.equalsIgnoreCase("INT") || columnType.equalsIgnoreCase("INTEGER") || columnType.equalsIgnoreCase("BIGINT")
                || columnType.equalsIgnoreCase("FLOAT") || columnType.equalsIgnoreCase("DOUBLE") || columnType.equalsIgnoreCase("BLOB")
                || columnType.equalsIgnoreCase("DECIMAL") || columnType.toUpperCase().contains("BLOB") || columnType.toUpperCase().contains("INT")) {
            return "Number";
        } else if (columnType.equalsIgnoreCase("TIMESTAMP") || columnType.equalsIgnoreCase("DATE") || columnType.equalsIgnoreCase("DATETIME")
                || columnType.toUpperCase().contains("DATE")) {
            return "DateTime";
        } else if (columnType.equalsIgnoreCase("BOOLEAN")) {
            return "Boolean";
        }
        return "String";
    }
}
